using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using database;
using objetos;

namespace dados
{
	#region TB_CAIXA
	/// <summary>
	/// This object represents the properties and methods of a TB_CAIXA.
	/// </summary>
	public class DAL_TB_CAIXA
	{		
	
		/// <summary>
		/// Incluir via aplicacao
		/// </summary>	
		public int Incluir(OBJ_TB_CAIXA obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				List<string> lista = new List<string>();		
	
				db.CreateParameter("@id_empresa", SqlDbType.Int, obj.id_empresa);				
				lista.Add("@id_empresa");				
				db.CreateParameter("@id_banco_conta_corrente", SqlDbType.Int, obj.id_banco_conta_corrente);				
				lista.Add("@id_banco_conta_corrente");				
				db.CreateParameter("@id_centro_custo", SqlDbType.Int, obj.id_centro_custo);				
				lista.Add("@id_centro_custo");				
				db.CreateParameter("@id_conta_contabil", SqlDbType.Int, obj.id_conta_contabil);				
				lista.Add("@id_conta_contabil");				
				db.CreateParameter("@id_origem", SqlDbType.Int, obj.id_origem);				
				lista.Add("@id_origem");				
				db.CreateParameter("@num_duplicata", SqlDbType.Int, obj.num_duplicata);				
				lista.Add("@num_duplicata");				
				db.CreateParameter("@num_cheque", SqlDbType.Int, obj.num_cheque);				
				lista.Add("@num_cheque");				
				db.CreateParameter("@documento", SqlDbType.Int, obj.documento);				
				lista.Add("@documento");				
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);				
				lista.Add("@descricao");				
				db.CreateParameter("@valor_entrada", SqlDbType.Decimal, obj.valor_entrada);				
				lista.Add("@valor_entrada");				
				db.CreateParameter("@valor_saida", SqlDbType.Decimal, obj.valor_saida);				
				lista.Add("@valor_saida");				
				db.CreateParameter("@dt_emissao", SqlDbType.DateTime, obj.dt_emissao);				
				lista.Add("@dt_emissao");				
				db.CreateParameter("@dt_vencimento", SqlDbType.DateTime, obj.dt_vencimento);				
				lista.Add("@dt_vencimento");				
				db.CreateParameter("@id_usuario", SqlDbType.Int, obj.id_usuario);				
				lista.Add("@id_usuario");				
	
				string query = String.Format("Insert Into tb_caixa ({0}) Values ({1}); SELECT SCOPE_IDENTITY()",  string.Join(",",lista.ToArray()).ToString().Replace("@", ""), string.Join(",",lista.ToArray()).ToString());
				ds = db.ExecuteQuery(query);
				
				return Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString());
			}
			catch (Exception)
       		{
            	throw;
        	}
		}
		
		/// <summary>
		/// Update via aplicacao
		/// </summary>			
		public void Alterar(OBJ_TB_CAIXA obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				List<string> lista = new List<string>();
	
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
				
	
				db.CreateParameter("@id_empresa", SqlDbType.Int, obj.id_empresa);
				lista.Add("id_empresa = @id_empresa");
				db.CreateParameter("@id_banco_conta_corrente", SqlDbType.Int, obj.id_banco_conta_corrente);
				lista.Add("id_banco_conta_corrente = @id_banco_conta_corrente");
				db.CreateParameter("@id_centro_custo", SqlDbType.Int, obj.id_centro_custo);
				lista.Add("id_centro_custo = @id_centro_custo");
				db.CreateParameter("@id_conta_contabil", SqlDbType.Int, obj.id_conta_contabil);
				lista.Add("id_conta_contabil = @id_conta_contabil");
				db.CreateParameter("@id_origem", SqlDbType.Int, obj.id_origem);
				lista.Add("id_origem = @id_origem");
				db.CreateParameter("@num_duplicata", SqlDbType.Int, obj.num_duplicata);
				lista.Add("num_duplicata = @num_duplicata");
				db.CreateParameter("@num_cheque", SqlDbType.Int, obj.num_cheque);
				lista.Add("num_cheque = @num_cheque");
				db.CreateParameter("@documento", SqlDbType.Int, obj.documento);
				lista.Add("documento = @documento");
				db.CreateParameter("@descricao", SqlDbType.VarChar, obj.descricao);
				lista.Add("descricao = @descricao");
				db.CreateParameter("@valor_entrada", SqlDbType.Decimal, obj.valor_entrada);
				lista.Add("valor_entrada = @valor_entrada");
				db.CreateParameter("@valor_saida", SqlDbType.Decimal, obj.valor_saida);
				lista.Add("valor_saida = @valor_saida");
				db.CreateParameter("@dt_emissao", SqlDbType.DateTime, obj.dt_emissao);
				lista.Add("dt_emissao = @dt_emissao");
				db.CreateParameter("@dt_vencimento", SqlDbType.DateTime, obj.dt_vencimento);
				lista.Add("dt_vencimento = @dt_vencimento");
				db.CreateParameter("@id_usuario", SqlDbType.Int, obj.id_usuario);
				lista.Add("id_usuario = @id_usuario");
	
				string query = String.Format("Update TB_CAIXA Set {0} Where id = @id", string.Join(",",lista.ToArray()).ToString());
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}	
		
		/// <summary>
		/// Delete viaaplicacao
		/// </summary>	
		public void Excluir(OBJ_TB_CAIXA obj)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				StringBuilder queryParameters = new StringBuilder();
				db.CreateParameter("@id", SqlDbType.Int, obj.id);
		
				string query = "Delete tb_caixa Where id = @id";			
				db.ExecuteQuery(query);
			}
			catch (Exception)
       		{
            	throw;
        	}
		}
	
	
		/// <summary>
		/// Lista objeto
		/// </summary>						
		public List<OBJ_TB_CAIXA> SelecionarLista()
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				List<OBJ_TB_CAIXA> lista = new List<OBJ_TB_CAIXA>();
				OBJ_TB_CAIXA obj = null;
				DataTable dt = new DataTable();
				DataSet ds = new DataSet();
				StringBuilder sb = new StringBuilder();
				sb.Append("Select * from  TB_CAIXA ");
        
	            ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
					{											
						obj = new OBJ_TB_CAIXA();
						Set(obj,dr);
						lista.Add(obj);
		
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}
		
		/// <summary>
		/// Set Objeto
		/// </summary>	
		private void Set(OBJ_TB_CAIXA obj,DataRow dr)
		{
			obj.id = Convert.ToInt32(dr["id"]);						
			obj.id_empresa = Convert.ToInt32(dr["id_empresa"].ToString()) ;	
			obj.id_banco_conta_corrente = Convert.ToInt32(dr["id_banco_conta_corrente"].ToString()) ;	
			obj.id_centro_custo = Convert.ToInt32(dr["id_centro_custo"].ToString()) ;	
			obj.id_conta_contabil = Convert.ToInt32(dr["id_conta_contabil"].ToString()) ;	
			obj.id_origem = Convert.ToInt32(dr["id_origem"].ToString()) ;	
			obj.num_duplicata = Convert.ToInt32(dr["num_duplicata"].ToString()) ;	
			obj.num_cheque = Convert.ToInt32(dr["num_cheque"].ToString()) ;	
			obj.documento = Convert.ToInt32(dr["documento"].ToString()) ;	
			obj.descricao = dr["descricao"] == DBNull.Value ?  string.Empty : dr["descricao"].ToString() ;
			obj.valor_entrada = dr["valor_entrada"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_entrada"].ToString()) ;
			obj.valor_saida = dr["valor_saida"] == DBNull.Value ?  0 : Convert.ToDecimal(dr["valor_saida"].ToString()) ;
			obj.dt_emissao = dr["dt_emissao"] == DBNull.Value ? (DateTime?)null :  Convert.ToDateTime(dr["dt_emissao"].ToString()) ;						
			obj.dt_vencimento = dr["dt_vencimento"] == DBNull.Value ? (DateTime?)null :  Convert.ToDateTime(dr["dt_vencimento"].ToString()) ;						
			obj.id_usuario = Convert.ToInt32(dr["id_usuario"].ToString()) ;	
			
		}						
				
		/// <summary>
		/// Selecionar Objeto Por ID
		/// </summary>	
		public OBJ_TB_CAIXA SelecionarObjetoPorId(int id)
		{
			try
			{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_CAIXA obj = new OBJ_TB_CAIXA();
				DataSet ds = new DataSet();				
				DataTable dt = new DataTable();
				db.CreateParameter("@id", SqlDbType.Int, id);
				ds = db.ExecuteQuery("SELECT * FROM tb_caixa  Where id = @id");				
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					
					foreach (DataRow dr in dt.Rows)
                	{
						obj = new OBJ_TB_CAIXA();
						Set(obj,dr);																			
					}
					return obj;
					
				}
				else
				{
					return null;	
				}			
			}
			catch (Exception)
       		{
            	throw;
        	}
			
		}
					
		
		
		/// <summary>
		/// Lista objeto Parametro Where OrderBy
		/// </summary>							
		public List<OBJ_TB_CAIXA> SelecionarLista(String Where, String OrderBy)
    	{
        	try
        	{
				DAO_DATABASE db = new DAO_DATABASE();
				OBJ_TB_CAIXA obj = null;
				DataSet ds = new DataSet();
				DataTable dt = new DataTable();
				List<OBJ_TB_CAIXA> lista = new List<OBJ_TB_CAIXA>();
				
				StringBuilder sb = new StringBuilder();
				db.CreateParameter("@Where",SqlDbType.VarChar, Where );
				db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy );				
				
				sb.Append("SELECT * ");
				sb.Append(" FROM TB_CAIXA");
				if(Where != string.Empty)				
				sb.Append(string.Format(" Where {0} ",Where));
				if(OrderBy != string.Empty)
				sb.Append(string.Format(" Order by {0}",OrderBy));
				
				ds = db.ExecuteQuery(sb.ToString());
				
				if(!db.DataSetIsEmpty(ds))
				{
					dt = ds.Tables[0];
					foreach (DataRow dr in dt.Rows)
					{						
						obj = new OBJ_TB_CAIXA();
						Set(obj,dr);			
						lista.Add(obj);
					}
				}
				return lista;
        	}
        	catch (Exception)
       		{
            	throw;
        	}

    	}

		
		public DataSet Selecionar(String Where, String OrderBy, int PageIndex, int PageSize )
		{
				DAO_DATABASE db = new DAO_DATABASE();
				DataSet ds = new DataSet();
				
				StringBuilder sb = new StringBuilder();
				
				PageIndex = PageSize * PageIndex;
            	PageSize =  PageIndex + PageSize;
				
		      	if (OrderBy == string.Empty) OrderBy = "id";

            	sb.Append("SELECT * FROM ");
            	sb.Append(string.Format(" (SELECT ROW_NUMBER() OVER (ORDER BY {0}) AS RowNumber,",OrderBy));
           		sb.Append(" * FROM   tb_caixa ");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				sb.Append(" ) query  ");
				sb.Append(string.Format(" WHERE RowNumber BETWEEN {0} + 1 and {1}; ",PageIndex,PageSize));

            	sb.Append("SELECT count(*) FROM tb_caixa");
				if (Where != string.Empty)
				{
					sb.Append(string.Format(" WHERE {0}", Where));
				}
				ds = db.ExecuteQuery(sb.ToString());
				
				if (!db.DataSetIsEmpty(ds))
            	{
                publico.PUB_PAGINACAO.CalcularTotalDePaginas(Convert.ToDecimal(ds.Tables[1].Rows[0][0].ToString()));
            	}

				
            	return ds;
								
		}
				
								
		public DataSet Selecionar(String Where, String OrderBy)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_CAIXA ");
           if (Where != string.Empty)
            {
                db.CreateParameter("@Where", SqlDbType.VarChar, Where);
                sb.Append(string.Format(" Where  {0}",Where));
            }

            if (OrderBy != string.Empty)
            {
                db.CreateParameter("@OrderBy", SqlDbType.VarChar, OrderBy);
                sb.Append(string.Format(" Order By {0}", OrderBy));
            }

            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		public DataSet Selecionar()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();

            sb.Append("Select * from  TB_CAIXA Order By id");            
            ds = db.ExecuteQuery(sb.ToString());
            return ds;
        }		
		
		
		public DataSet ListaCampos()
        {

            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            sb.Append("SELECT TABLE_NAME, ORDINAL_POSITION, ");
            sb.Append(" COLUMN_NAME, DATA_TYPE, IS_NULLABLE ");
            sb.Append(" FROM INFORMATION_SCHEMA.COLUMNS");
            sb.Append(" WHERE TABLE_NAME = 'tb_caixa'");
            ds = db.ExecuteQuery(sb.ToString());

            return ds;

        }

		public DataSet DropdownList()
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
			sb.Append("SELECT ");
			sb.Append(" id ");
				sb.Append(", id_empresa ");  				
				sb.Append(", id_banco_conta_corrente ");  				
				sb.Append(", id_centro_custo ");  				
			sb.Append(" FROM tb_caixa ");           
			sb.Append(" ORDER BY id_empresa ");
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_CAIXA_DROPDOWNLIST");  
            return ds;
        }

        public DataSet DropdownList(String Where)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            db.CreateParameter("@Where", SqlDbType.VarChar, Where);
			sb.Append("SELECT ");
			sb.Append(" id ");
			
			
			sb.Append(", id_empresa ");  				
			
			
			sb.Append(", id_banco_conta_corrente ");  				
			
			
			sb.Append(", id_centro_custo ");  				
			sb.Append(" FROM tb_caixa ");
			sb.Append(string.Format(" WHERE {0} ",Where));
			sb.Append(" ORDER BY id_empresa ");
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_CAIXA_DROPDOWNLIST");
            return ds;
        }
		
		  public DataSet DropdownList(String Where, String OrderBy)
        {
            DAO_DATABASE db = new DAO_DATABASE();
            DataSet ds = new DataSet();
            StringBuilder sb = new StringBuilder();
            db.CreateParameter("@Where", SqlDbType.VarChar, Where);
			sb.Append("SELECT ");
			sb.Append(" id ");
			sb.Append(", id_empresa ");  				
			sb.Append(", id_banco_conta_corrente ");  				
			sb.Append(", id_centro_custo ");  				
			sb.Append(" FROM tb_caixa ");
			sb.Append(string.Format(" WHERE {0} ORDER BY {1}",Where,OrderBy));			
            ds = db.ExecuteQuery(sb.ToString());

            // ds = db.ExecuteProcedure("USP_TB_CAIXA_DROPDOWNLIST");
            return ds;
        }



          public DataTable CriarDataTable()
          {
              DataTable table = new DataTable();
              table.Columns.Add("id", typeof(int));
              table.Columns.Add("id_empresa", typeof(int));
              table.Columns.Add("id_banco_conta_corrente", typeof(int));
              table.Columns.Add("id_centro_custo", typeof(int));
              table.Columns.Add("id_conta_contabil", typeof(int));
              table.Columns.Add("id_origem", typeof(int));
              table.Columns.Add("num_duplicata", typeof(int));
              table.Columns.Add("num_cheque", typeof(int));
              table.Columns.Add("documento", typeof(string));
              table.Columns.Add("descricao", typeof(string));
              table.Columns.Add("valor_entrada", typeof(string));
              table.Columns.Add("valor_saida", typeof(string));
              table.Columns.Add("dt_emissao", typeof(DateTime));
              table.Columns.Add("dt_vencimento", typeof(DateTime));
              table.Columns.Add("id_usuario", typeof(int));
              return table;
          }

          public DataTable PreencheDataTable(DataTable dt, OBJ_TB_CAIXA obj)
          {
              DataRow dr = dt.NewRow();
              dr["id"] = obj.id.ToString();
              dr["id_empresa"] = obj.id_empresa.ToString();
              dr["id_banco_conta_corrente"] = obj.id_banco_conta_corrente.ToString();
              dr["id_centro_custo"] = obj.id_centro_custo.ToString();
              dr["id_conta_contabil"] = obj.id_conta_contabil.ToString();
              dr["id_origem"] = obj.id_origem.ToString();
              dr["num_duplicata"] = obj.num_duplicata.ToString();
              dr["num_cheque"] = obj.num_cheque.ToString();
              dr["documento"] = obj.documento.ToString();
              dr["descricao"] = obj.descricao.ToString();
              dr["valor_entrada"] = obj.valor_entrada.ToString();
              dr["valor_saida"] = obj.valor_saida.ToString();
              dr["dt_emissao"] = obj.dt_emissao.ToString();
              dr["dt_vencimento"] = obj.dt_vencimento.ToString();
              dr["id_usuario"] = obj.id_usuario.ToString();

              dt.Rows.Add(dr);
              return dt;

          }



	}
	#endregion
}

